﻿Public Class mainForm

    Dim tt As New ToolTip
    Dim sqlbuilder As sqlBuilder
    Dim where_Save_Mode As Boolean
    Dim currentWhereObj As sqlWhere = Nothing

    Dim WithEvents listBoxCMS As New ContextMenuStrip

    Dim funcListStartlocationY

    Dim WithEvents deleteBut As ToolStripItem

    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        Try

      
            sqlbuilder = New sqlBuilder(Me.selectBox, Me.orderByBox)

            limitBox.SelectedIndex = 1
            refreshSelect()

            whereListBox.ContextMenuStrip = listBoxCMS
            funcListBox.ContextMenuStrip = listBoxCMS

            TableList.HorizontalScrollbar = True

            funcListStartlocationY = Me.funcListBox.Top


            '            Dim pf As New parseForm
            '           pf.Show()




            mainForm_Resize()

            If Len(My.Settings.userName) = 0 Then
                Dim pu As New credentials
                pu.ShowDialog()
            End If

        Catch ex As Exception
            XBRL.onerror(ex)
        End Try


    End Sub

    Private Sub refreshSelect()
        Try

            TableList.Enabled = True
            TableList.Items.Clear()


            ' Me.selectPage.Controls.Clear()
            Dim removeControlList As New List(Of Object) ' clear tables only!
            For Each c In selectPage.Controls

                If TryCast(c, ListBox) Is Nothing And _
                TryCast(c, TextBox) Is Nothing And _
                 TryCast(c, Label) Is Nothing Then removeControlList.Add(c)
            Next

            For Each o In removeControlList
                selectPage.Controls.Remove(o)
            Next

            If sqlbuilder.tables.Count = 0 Then
                For Each t In XBRL.tables
                    TableList.Items.Add(t)
                Next
            Else


                Dim xx As Integer = 4
                For Each t As sqlTable In sqlbuilder.tables
                    'add a panel

                    If t.myControl Is Nothing Then
                        'create a control


                        t.myControl = New SqlTableCntrl(t)
                        AddHandler t.myControl.c_deleteKeyPressed, AddressOf deleteTable
                        AddHandler t.myControl.c_SqlSelectsChanged, AddressOf fieldSelectionChanged
                        AddHandler t.myControl.c_selected, AddressOf tableSelected
                    End If

                    'add the control to the panel

                    Me.selectPage.Controls.Add(t.myControl)
                    t.myControl.allowDelete = True
                    t.myControl.Left = 0
                    t.myControl.Top = 0
                Next



                Dim ylevel As Integer = 0
                Dim sortedTables As sqlTables = SortTables()
                Dim lastBottom As Integer
                For Each t As sqlTable In sortedTables

                    t.myControl.selectSelects()


                    t.myControl.Left = 4 + t.indentLevel * (t.myControl.Width + 8)

                    Dim indx As Integer = sortedTables.indexOf(t)
                    'if this is NOT on the same subthread as the previous table....
                    If indx > 0 AndAlso t.indentLevel <> sortedTables.Item(indx - 1).indentLevel + 1 Then
                        'go to next line
                        ylevel = ylevel + 1

                    End If

                    t.myControl.Top = ylevel * (t.myControl.Height + 8)


                    lastBottom = t.myControl.Top + t.myControl.Height


                Next



                refreshQuery()

                'update the list with tables that can join with the current table
                Dim currtable As sqlTable = sqlbuilder.tables.selectedTable
                For Each obj As sqlTable In currtable.getJoinList()
                    TableList.Items.Add(obj)
                Next

                TableList.Items.Add("")
                TableList.Items.Add("FUNCTIONS:")

                'now add functions!

                For Each obj As sqlFunction In sqlFunction.getFunctionList()
                    TableList.Items.Add(obj)
                Next


                Dim funcs As New List(Of String)
                funcs.AddRange(sqlbuilder.DISTINCTfunctions)  ' must go first!!!
                funcs.AddRange(sqlbuilder.SQLfunctions)
                funcListBox.Items.Clear()
                For Each s As String In funcs
                    funcListBox.Items.Add(s)
                Next

                funcListBox.Top = Math.Max(Me.funcListStartlocationY, (lastBottom + 8))

            End If

        Catch ex As Exception
            XBRL.onerror(ex)
        End Try
    End Sub

    Sub refreshQuery()
        SQLTextBox.Text = sqlbuilder.ToString
    End Sub


    Private Sub fieldSelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
        Try
            Dim stc As SqlTableCntrl = TryCast(sender, SqlTableCntrl)

            If sqlTabControl.SelectedTab Is selectPage Then
                stc.table.SQLselects.Clear()
                For Each f As SQLfield In stc.selectList.SelectedItems
                    stc.table.SQLselects.Add(f)
                Next
                refreshQuery()



            ElseIf sqlTabControl.SelectedTab Is wherePage Then
                Dim F As SQLfield = TryCast(stc.selectList.SelectedItem, SQLfield)

                whereTextBox.Text = whereTextBox.Text & stc.table.shortName & "." & F.fieldName & " "
                stc.selectWheres()

            End If


        Catch ex As Exception
            XBRL.onerror(ex)
        End Try

    End Sub


    Private Sub refreshWhere()
        TableList.Enabled = False


        '        wherePage.Controls.Clear()
        Dim removeControlList As New List(Of Object)
        For Each c In wherePage.Controls
            If TryCast(c, sqlTable) IsNot Nothing Then removeControlList.Add(c)
        Next

        For Each o In removeControlList
            wherePage.Controls.Remove(o)
        Next

        Dim x As Integer = 4
        For Each t As sqlTable In sqlbuilder.tables
            wherePage.Controls.Add(t.myControl)
            t.myControl.allowDelete = False

            t.myControl.Left = x
            t.myControl.Top = 0
            x = x + 8 + t.myControl.Width

            t.myControl.selectWheres()

        Next
        If sqlbuilder.tables.Count = 0 Then
            whereButton.Visible = False
        Else
            whereButton.Visible = True
            ' whereButton.Top = sqlbuilder.tables.Item(0).myControl.Bottom + 8

        End If

        whereListBox.Items.Clear()
        For Each s As sqlWhere In sqlbuilder.SQLwheres
            whereListBox.Items.Add(s)
        Next

        whereSaveMode(True, Nothing)
        refreshQuery()
    End Sub



    Function SortTables() As sqlTables
        ' sort posts
        Dim tablesINorder = New sqlTables
        Dim foundIt As Boolean


        tablesINorder.Add(sqlbuilder.tables.Item(0))
        For Counter = 1 To sqlbuilder.tables.Count - 1

            If sqlbuilder.tables.Item(Counter).joinOtherTable Is sqlbuilder.tables.Item(Counter - 1) Then
                'it goes right after the last one and indentlevel++
                sqlbuilder.tables.Item(Counter).indentLevel = sqlbuilder.tables.Item(Counter - 1).indentLevel + 1
                tablesINorder.Add(sqlbuilder.tables.Item(Counter))
            ElseIf sqlbuilder.tables.Item(Counter).joinOtherTable Is sqlbuilder.tables.Item(Counter - 1).joinOtherTable Then
                'it goes right after the last one, same indentlevel
                sqlbuilder.tables.Item(Counter).indentLevel = sqlbuilder.tables.Item(Counter - 1).indentLevel
                tablesINorder.Add(sqlbuilder.tables.Item(Counter))
            Else

                For ctr2 = 0 To tablesINorder.Count - 1
                    'first find the post it is a response to
                    If tablesINorder.Item(ctr2) Is sqlbuilder.tables.Item(Counter).joinOtherTable Then
                        ' indentlevel++
                        sqlbuilder.tables.Item(Counter).indentLevel = tablesINorder.Item(ctr2).indentLevel + 1

                        'then put it after all other reponses and sub responses of that one. 
                        foundIt = False
                        For pstctr3 = ctr2 + 1 To tablesINorder.Count - 1
                            'if indent level is less than me, I went too far
                            If sqlbuilder.tables.Item(Counter).indentLevel > tablesINorder.Item(pstctr3).indentLevel Then
                                tablesINorder.addAt(pstctr3, sqlbuilder.tables.Item(Counter))
                                foundIt = True
                                Exit For
                            End If
                        Next
                        If Not foundIt Then tablesINorder.Add(sqlbuilder.tables.Item(Counter))
                        Exit For
                    End If
                Next
            End If
        Next

        Return tablesINorder

    End Function


    Private Sub TableList_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles TableList.MouseDoubleClick
        Try

            Dim i As Integer = TableList.IndexFromPoint(New Point(e.X, e.Y))
            If (i >= 0 And i < TableList.Items.Count) Then

                Dim t As sqlTable = TryCast(TableList.Items(i), sqlTable)
                If t Is Nothing Then Exit Sub
                Select Case t.itemType
                    Case sqlTable.itemTypes.table
                        If sqlbuilder.addTable(t, sqlbuilder.tables.selectedTable) Then table_selected(t)
                        refreshSelect()

                    Case sqlTable.itemTypes.fromFunction
                        'acts like a table sort of
                        MsgBox("don't know what to do with this yet")

                    Case sqlTable.itemTypes.selectFunction

                        '                       goes in the select or where clause
                        '         functionTextBox.text = t.tableName & "()"
                        Dim fpu As New functionPopUp(t, sqlbuilder)
                        If fpu.ShowDialog() = Windows.Forms.DialogResult.OK Then

                            If t.tableName = "distinct" Then
                                sqlbuilder.DISTINCTfunctions.Add(fpu.functionComboBox.Text)
                            Else
                                sqlbuilder.SQLfunctions.Add(fpu.functionComboBox.Text)
                            End If
                            refreshSelect()

                        End If
                End Select



            End If



        Catch ex As Exception
            XBRL.onerror(ex)
        End Try

    End Sub





    Private Sub createToolTips(ByVal sender As Object, ByVal e As System.EventArgs) Handles TableList.MouseHover
        'the tool tips!

        Dim Point As Point = PointToClient(Windows.Forms.Cursor.Position) - TableList.Location  '(e.X, e.Y)
        ' MsgBox(Point.X & "   " & Point.Y)

        Dim hoverIndex As Integer = TableList.IndexFromPoint(Point)
        If (hoverIndex >= 0 And hoverIndex < TableList.Items.Count) Then

            Dim st As sqlTable = TryCast(TableList.Items(hoverIndex), sqlTable)
            If st IsNot Nothing Then tt.SetToolTip(TableList, st.tableDescription)
        End If
    End Sub


    Private Sub goButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles goButton.Click
        Dim dbp As New dbaseProc(SQLTextBox.Text)
        dbp.Show()
    End Sub

    Private Sub deleteTable(ByVal sender As Object, ByVal e As EventArgs)
        Try
            Dim t As sqlTable = TryCast(sender, sqlTable)
            Dim sortedTables As sqlTables = SortTables()
            Dim indx As Integer = sortedTables.indexOf(t)



            If indx > 0 Then ' select the previous table
                Dim st As sqlTable = sortedTables.Item(indx - 1)
                Me.table_selected(st)
            End If

            If indx >= 0 Then
                'remove table and all after it in the same thread!
                sqlbuilder.tables.Remove(t)
                indx = indx + 1
                Do While indx < sortedTables.Count AndAlso sortedTables.Item(indx).indentLevel > t.indentLevel  '= sortedTables.Item(indx - 1).indentLevel + 1
                    sqlbuilder.tables.Remove(sortedTables.Item(indx))
                    indx = indx + 1
                Loop



            End If

            refreshSelect()
        Catch ex As Exception
            XBRL.onerror(ex)
        End Try

    End Sub

    Private Sub tableSelected(ByVal sender As Object, ByVal e As EventArgs)
        If sqlTabControl.SelectedTab IsNot selectPage Then Exit Sub
        Dim t As sqlTable = TryCast(sender, sqlTable)
        If t.selected Then Exit Sub
        table_selected(t)
        refreshSelect()
    End Sub

    Private Sub table_selected(ByRef t As sqlTable)


        For Each tt As sqlTable In sqlbuilder.tables
            tt.selected = False
        Next

        t.selected = True
        ' refreshMe()  ' no don't do this
    End Sub


    Private Sub limitBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles limitBox.SelectedIndexChanged
        sqlbuilder.limit = 0
        If IsNumeric(limitBox.SelectedItem.ToString) Then sqlbuilder.limit = CInt(limitBox.SelectedItem.ToString)
        refreshQuery()
    End Sub


    Private Sub sqlTabControl_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles sqlTabControl.SelectedIndexChanged

        If sqlTabControl.SelectedTab Is wherePage Then refreshWhere()
        If sqlTabControl.SelectedTab Is selectPage Then refreshSelect()
        mainForm_Resize()
    End Sub

    Private Sub whereButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles whereButton.Click


        If where_Save_Mode Then
            sqlbuilder.SQLwheres.Add(New sqlWhere(whereTextBox.Text))
        Else
            currentWhereObj.text = whereTextBox.Text

        End If



        refreshWhere()
    End Sub


    Public Sub whereSaveMode(ByVal saveNew As Boolean, ByVal where As sqlWhere)
        where_Save_Mode = saveNew
        If saveNew Then
            whereButton.Text = "Add Where Clause"
            whereTextBox.Text = ""
        Else
            whereButton.Text = "Save Changes"
            currentWhereObj = where
            whereTextBox.Text = where.text
        End If


    End Sub

    Private Sub whereListBox_DoubleClick(ByVal sender As Object, ByVal e As MouseEventArgs) Handles whereListBox.MouseDoubleClick
        Dim indx As Integer = whereListBox.IndexFromPoint(New Point(e.X, e.Y))
        If indx >= 0 Then
            Dim where As sqlWhere = whereListBox.Items(indx)
            whereSaveMode(False, where)

        End If
    End Sub

    Private Sub listBoxCMS_Opening(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles listBoxCMS.Opening
        listBoxCMS.Items.Clear()

        Dim strip As ContextMenuStrip = sender
        Dim lb As ListBox = strip.SourceControl


     



        '  Dim lb As ListBox = TryCast(sender.owner, ListBox)

        Dim p As Point = lb.PointToClient(Windows.Forms.Cursor.Position) ' - lb.Location - sqlTabControl.Location - System.Windows.Forms.SystemInformation.BorderSize  ' - sqlTabControl.ItemSize - 

        Dim indx As Integer = lb.IndexFromPoint(p)



        If indx >= 0 Then
            deleteBut = listBoxCMS.Items.Add("Delete")
            deleteBut.Tag = lb.Items(indx)
            e.Cancel = False
        Else
            e.Cancel = True
        End If

    End Sub

    Private Sub deleteBut_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles deleteBut.Click
        Dim sqlWhere As sqlWhere = TryCast(sender.tag, sqlWhere)

        If sqlWhere IsNot Nothing Then
            sqlbuilder.SQLwheres.Remove(sqlWhere)
            refreshWhere()
        Else
            Dim sqlFunc As String = TryCast(sender.tag, String)

            sqlbuilder.DISTINCTfunctions.Remove(sqlFunc)

            sqlbuilder.SQLfunctions.Remove(sqlFunc)
            refreshSelect()
        End If

    End Sub

    Private Sub mainForm_Resize() Handles Me.Resize
        Me.SQLTextBox.Top = Me.ClientSize.Height - 4 - Me.SQLTextBox.Height
        Me.TableList.Height = Me.SQLTextBox.Top - 8 - Me.TableList.Top
        Me.sqlTabControl.Width = Me.ClientSize.Width - 4 - Me.sqlTabControl.Left


        Me.sqlTabControl.Height = Me.SQLTextBox.Top - 8 - Me.sqlTabControl.Top

        goButton.Top = SQLTextBox.Top
        limitBox.Top = goButton.Bottom + 12

        goButton.Left = Me.ClientSize.Width - 4 - Me.goButton.Width
        limitBox.Left = Me.ClientSize.Width - 4 - Me.limitBox.Width
        SQLTextBox.Width = Me.goButton.Left - 4 - SQLTextBox.Left


        Me.funcListBox.Width = Me.selectPage.Width - 2 - Me.funcListBox.Left
        Me.selectBox.Width = Me.selectPage.Width - 2 - Me.selectBox.Left

        whereTextBox.Width = Me.wherePage.Width - 2 - Me.whereTextBox.Left
        whereListBox.Width = Me.wherePage.Width - 2 - Me.whereListBox.Left
        orderByBox.Width = Me.wherePage.Width - 2 - Me.orderByBox.Left
    End Sub

    Private Sub selectBox_KeyUp(sender As Object, e As System.Windows.Forms.KeyEventArgs) Handles selectBox.KeyUp, orderByBox.KeyUp
        refreshQuery()
    End Sub

























End Class
